✕

PostgreSQL Physical Model

Schema for:

Model name: New model
Author:
Version:
File name: Zendesk PostgreSQL model.hck.json
File path: /Users/rvanbruggen/Library/CloudStorage/OneDrive-Hackolade/Demo/HackoladeGithubRepo/Zendesk PostgreSQL model.hck.json
Printed On: Thu Jun 08 2023 19:09:14 GMT+0200 (Central European Summer Time)
Created with: Hackolade - Polyglot data modeling for NoSQL databases, storage formats, REST APIs, and JSON in RDBMS
1. Model
1.1 Model New model
1.1.1 New model Entity Relationship Diagram
1.1.2 New model Properties
1.1.2.1 Details tab
PropertyValue
Model nameNew model
Technical name
Description
Author
Version
Synchronization Id
DB vendorPostgreSQL
DB versionv13.x
Database namedvdrental
Tablespacepg_default
EncodingUTF8
Template
Locale
Collationen_US.UTF-8
Character typeen_US.UTF-8
Lineage capturetrue
Polyglot models
Comments
1.1.2.2 Lineage capture tab
PropertyValue
Model sources
[1] Source nameRE'd from instance
Source ID17369186-dae8-4af8-9db5-b86e765aaf0e
Description
Source formatInstance
Connection nameHackoladeLocalPostgres
Timestamp2023-06-08 18:56:12 CEST
1.1.3 New model User-Defined Types
1.1.3.1 Column mpaa_rating
1.1.3.1.1 mpaa_rating Tree Diagram
1.1.3.1.2 mpaa_rating properties
PropertyValue
Business Name mpaa_rating
Technical name
Id
Type enum
Comments
Not null
Pattern
Default
Enum G,PG,PG-13,R,NC-17
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
1.1.3.2 Column year
1.1.3.2.1 year Tree Diagram
1.1.3.2.2 year properties
PropertyValue
Business Name year
Technical name
Id
Type domain
Underlying type integer
Collation
Not null false
Default
Check constraints
[1] Constraint nameyear_check
Check expression((VALUE >= 1901) AND (VALUE <= 2155))
Comments
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2. Schemas
2.1 Schema public
2.1.1 public Properties
PropertyValue
Schema namepublic
Technical name
Activatedtrue
Comments
If not existtrue
Remarks
2.1.2 public Tables
2.1.2.1 Table actor
2.1.2.1.1 actor Tree Diagram
2.1.2.1.2 actor Properties
PropertyValue
Tableactor
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.1.3 actor Column
ColumnTypeReqKeyDescriptionComments
actor_idintegertruepk, dk
first_namevarchar(45)true
last_namevarchar(45)true
last_updatetimestamptrue
2.1.2.1.3.1 Column actor_id
2.1.2.1.3.1.1 actor_id Tree Diagram
2.1.2.1.3.1.2 actor_id properties
PropertyValue
Business Name actor_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('actor_actor_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint nameactor_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.1.3.2 Column first_name
2.1.2.1.3.2.1 first_name Tree Diagram
2.1.2.1.3.2.2 first_name properties
PropertyValue
Business Name first_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.1.3.3 Column last_name
2.1.2.1.3.3.1 last_name Tree Diagram
2.1.2.1.3.3.2 last_name properties
PropertyValue
Business Name last_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.1.3.4 Column last_update
2.1.2.1.3.4.1 last_update Tree Diagram
2.1.2.1.3.4.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.1.4 actor Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.1.5 actor Indexes
2.1.2.1.5.1 Index
Property
Nameactor_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
actor_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.1.5.2 Index
Property
Nameidx_actor_last_name
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
last_name
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.1.6 actor Triggers
2.1.2.1.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.1.7 actor JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "actor",
    "properties": {
        "actor_id": {
            "type": "number",
            "default": "nextval('actor_actor_id_seq'::regclass)"
        },
        "first_name": {
            "type": "string"
        },
        "last_name": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "actor_id",
        "first_name",
        "last_name",
        "last_update"
    ]
}
2.1.2.1.8 actor JSON data
{
    "actor_id": "nextval('actor_actor_id_seq'::regclass)",
    "first_name": "Lorem",
    "last_name": "Lorem",
    "last_update": "now()"
}
2.1.2.1.9 actor Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.actor (
	actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.actor
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS actor_pkey
 ON ONLY public.actor USING BTREE (actor_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_actor_last_name
 ON ONLY public.actor USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
2.1.2.2 Table address
2.1.2.2.1 address Tree Diagram
2.1.2.2.2 address Properties
PropertyValue
Tableaddress
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.2.3 address Column
ColumnTypeReqKeyDescriptionComments
address_idintegertruepk, dk
addressvarchar(50)true
address2varchar(50)false
districtvarchar(20)true
city_idsmallinttruefk
postal_codevarchar(10)false
phonevarchar(20)true
last_updatetimestamptrue
2.1.2.2.3.1 Column address_id
2.1.2.2.3.1.1 address_id Tree Diagram
2.1.2.2.3.1.2 address_id properties
PropertyValue
Business Name address_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('address_address_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint nameaddress_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.2 Column address
2.1.2.2.3.2.1 address Tree Diagram
2.1.2.2.3.2.2 address properties
PropertyValue
Business Name address
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.3 Column address2
2.1.2.2.3.3.1 address2 Tree Diagram
2.1.2.2.3.3.2 address2 properties
PropertyValue
Business Name address2
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.4 Column district
2.1.2.2.3.4.1 district Tree Diagram
2.1.2.2.3.4.2 district properties
PropertyValue
Business Name district
Technical name
Activated true
Id
Type char
Subtype varchar
Length 20
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.5 Column city_id
2.1.2.2.3.5.1 city_id Tree Diagram
2.1.2.2.3.5.2 city_id properties
PropertyValue
Business Name city_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table city
Foreign field city_id
Relationship type Foreign Key
Relationship name fk_address_city
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.6 Column postal_code
2.1.2.2.3.6.1 postal_code Tree Diagram
2.1.2.2.3.6.2 postal_code properties
PropertyValue
Business Name postal_code
Technical name
Activated true
Id
Type char
Subtype varchar
Length 10
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.7 Column phone
2.1.2.2.3.7.1 phone Tree Diagram
2.1.2.2.3.7.2 phone properties
PropertyValue
Business Name phone
Technical name
Activated true
Id
Type char
Subtype varchar
Length 20
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.3.8 Column last_update
2.1.2.2.3.8.1 last_update Tree Diagram
2.1.2.2.3.8.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.2.4 address Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.2.5 address Indexes
2.1.2.2.5.1 Index
Property
Nameaddress_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
address_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.2.5.2 Index
Property
Nameidx_fk_city_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
city_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.2.6 address Triggers
2.1.2.2.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.2.7 address JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "address",
    "properties": {
        "address_id": {
            "type": "number",
            "default": "nextval('address_address_id_seq'::regclass)"
        },
        "address": {
            "type": "string"
        },
        "address2": {
            "type": "string"
        },
        "district": {
            "type": "string"
        },
        "city_id": {
            "type": "number"
        },
        "postal_code": {
            "type": "string"
        },
        "phone": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "address_id",
        "address",
        "district",
        "city_id",
        "phone",
        "last_update"
    ]
}
2.1.2.2.8 address JSON data
{
    "address_id": "nextval('address_address_id_seq'::regclass)",
    "address": "Lorem",
    "address2": "Lorem",
    "district": "Lorem",
    "city_id": 7,
    "postal_code": "Lorem",
    "phone": "Lorem",
    "last_update": "now()"
}
2.1.2.2.9 address Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.address (
	address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL,
	address varchar(50) NOT NULL,
	address2 varchar(50),
	district varchar(20) NOT NULL,
	city_id smallint NOT NULL,
	postal_code varchar(10),
	phone varchar(20) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT address_pkey PRIMARY KEY (address_id),
	CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city (city_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.address
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS address_pkey
 ON ONLY public.address USING BTREE (address_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_city_id
 ON ONLY public.address USING BTREE (city_id pg_catalog.int2_ops ASC NULLS LAST) ;
2.1.2.3 Table category
2.1.2.3.1 category Tree Diagram
2.1.2.3.2 category Properties
PropertyValue
Tablecategory
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.3.3 category Column
ColumnTypeReqKeyDescriptionComments
category_idintegertruepk, dk
namevarchar(25)true
last_updatetimestamptrue
2.1.2.3.3.1 Column category_id
2.1.2.3.3.1.1 category_id Tree Diagram
2.1.2.3.3.1.2 category_id properties
PropertyValue
Business Name category_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('category_category_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namecategory_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.3.3.2 Column name
2.1.2.3.3.2.1 name Tree Diagram
2.1.2.3.3.2.2 name properties
PropertyValue
Business Name name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 25
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.3.3.3 Column last_update
2.1.2.3.3.3.1 last_update Tree Diagram
2.1.2.3.3.3.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.3.4 category Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.3.5 category Indexes
2.1.2.3.5.1 Index
Property
Namecategory_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
category_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.3.6 category Triggers
2.1.2.3.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.3.7 category JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "category",
    "properties": {
        "category_id": {
            "type": "number",
            "default": "nextval('category_category_id_seq'::regclass)"
        },
        "name": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "category_id",
        "name",
        "last_update"
    ]
}
2.1.2.3.8 category JSON data
{
    "category_id": "nextval('category_category_id_seq'::regclass)",
    "name": "Lorem",
    "last_update": "now()"
}
2.1.2.3.9 category Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.category (
	category_id integer DEFAULT nextval('category_category_id_seq'::regclass) NOT NULL,
	name varchar(25) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT category_pkey PRIMARY KEY (category_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.category
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS category_pkey
 ON ONLY public.category USING BTREE (category_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.4 Table city
2.1.2.4.1 city Tree Diagram
2.1.2.4.2 city Properties
PropertyValue
Tablecity
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.4.3 city Column
ColumnTypeReqKeyDescriptionComments
city_idintegertruepk, dk
cityvarchar(50)true
country_idsmallinttruefk
last_updatetimestamptrue
2.1.2.4.3.1 Column city_id
2.1.2.4.3.1.1 city_id Tree Diagram
2.1.2.4.3.1.2 city_id properties
PropertyValue
Business Name city_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('city_city_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namecity_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.4.3.2 Column city
2.1.2.4.3.2.1 city Tree Diagram
2.1.2.4.3.2.2 city properties
PropertyValue
Business Name city
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.4.3.3 Column country_id
2.1.2.4.3.3.1 country_id Tree Diagram
2.1.2.4.3.3.2 country_id properties
PropertyValue
Business Name country_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table country
Foreign field country_id
Relationship type Foreign Key
Relationship name fk_city
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.4.3.4 Column last_update
2.1.2.4.3.4.1 last_update Tree Diagram
2.1.2.4.3.4.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.4.4 city Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.4.5 city Indexes
2.1.2.4.5.1 Index
Property
Namecity_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
city_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.4.5.2 Index
Property
Nameidx_fk_country_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
country_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.4.6 city Triggers
2.1.2.4.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.4.7 city JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "city",
    "properties": {
        "city_id": {
            "type": "number",
            "default": "nextval('city_city_id_seq'::regclass)"
        },
        "city": {
            "type": "string"
        },
        "country_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "city_id",
        "city",
        "country_id",
        "last_update"
    ]
}
2.1.2.4.8 city JSON data
{
    "city_id": "nextval('city_city_id_seq'::regclass)",
    "city": "Lorem",
    "country_id": 66,
    "last_update": "now()"
}
2.1.2.4.9 city Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.city (
	city_id integer DEFAULT nextval('city_city_id_seq'::regclass) NOT NULL,
	city varchar(50) NOT NULL,
	country_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT city_pkey PRIMARY KEY (city_id),
	CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES public.country (country_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.city
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS city_pkey
 ON ONLY public.city USING BTREE (city_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_country_id
 ON ONLY public.city USING BTREE (country_id pg_catalog.int2_ops ASC NULLS LAST) ;
2.1.2.5 Table country
2.1.2.5.1 country Tree Diagram
2.1.2.5.2 country Properties
PropertyValue
Tablecountry
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.5.3 country Column
ColumnTypeReqKeyDescriptionComments
country_idintegertruepk, dk
countryvarchar(50)true
last_updatetimestamptrue
2.1.2.5.3.1 Column country_id
2.1.2.5.3.1.1 country_id Tree Diagram
2.1.2.5.3.1.2 country_id properties
PropertyValue
Business Name country_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('country_country_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namecountry_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.5.3.2 Column country
2.1.2.5.3.2.1 country Tree Diagram
2.1.2.5.3.2.2 country properties
PropertyValue
Business Name country
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.5.3.3 Column last_update
2.1.2.5.3.3.1 last_update Tree Diagram
2.1.2.5.3.3.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.5.4 country Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.5.5 country Indexes
2.1.2.5.5.1 Index
Property
Namecountry_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
country_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.5.6 country Triggers
2.1.2.5.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.5.7 country JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "country",
    "properties": {
        "country_id": {
            "type": "number",
            "default": "nextval('country_country_id_seq'::regclass)"
        },
        "country": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "country_id",
        "country",
        "last_update"
    ]
}
2.1.2.5.8 country JSON data
{
    "country_id": "nextval('country_country_id_seq'::regclass)",
    "country": "Lorem",
    "last_update": "now()"
}
2.1.2.5.9 country Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.country (
	country_id integer DEFAULT nextval('country_country_id_seq'::regclass) NOT NULL,
	country varchar(50) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT country_pkey PRIMARY KEY (country_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.country
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS country_pkey
 ON ONLY public.country USING BTREE (country_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.6 Table customer
2.1.2.6.1 customer Tree Diagram
2.1.2.6.2 customer Properties
PropertyValue
Tablecustomer
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.6.3 customer Column
ColumnTypeReqKeyDescriptionComments
customer_idintegertruepk, dk
store_idsmallinttrue
first_namevarchar(45)true
last_namevarchar(45)true
emailvarchar(50)false
address_idsmallinttruefk
activeboolbooleantrue
create_datedatetrue
last_updatetimestampfalse
activeintegerfalse
2.1.2.6.3.1 Column customer_id
2.1.2.6.3.1.1 customer_id Tree Diagram
2.1.2.6.3.1.2 customer_id properties
PropertyValue
Business Name customer_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('customer_customer_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namecustomer_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.2 Column store_id
2.1.2.6.3.2.1 store_id Tree Diagram
2.1.2.6.3.2.2 store_id properties
PropertyValue
Business Name store_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.3 Column first_name
2.1.2.6.3.3.1 first_name Tree Diagram
2.1.2.6.3.3.2 first_name properties
PropertyValue
Business Name first_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.4 Column last_name
2.1.2.6.3.4.1 last_name Tree Diagram
2.1.2.6.3.4.2 last_name properties
PropertyValue
Business Name last_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.5 Column email
2.1.2.6.3.5.1 email Tree Diagram
2.1.2.6.3.5.2 email properties
PropertyValue
Business Name email
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.6 Column address_id
2.1.2.6.3.6.1 address_id Tree Diagram
2.1.2.6.3.6.2 address_id properties
PropertyValue
Business Name address_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table address
Foreign field address_id
Relationship type Foreign Key
Relationship name customer_address_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.7 Column activebool
2.1.2.6.3.7.1 activebool Tree Diagram
2.1.2.6.3.7.2 activebool properties
PropertyValue
Business Name activebool
Technical name
Activated true
Id
Type boolean
Comments
Array type
Not null true
Default true
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.8 Column create_date
2.1.2.6.3.8.1 create_date Tree Diagram
2.1.2.6.3.8.2 create_date properties
PropertyValue
Business Name create_date
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Array type
Not null true
Default ('now'::text)::date
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.9 Column last_update
2.1.2.6.3.9.1 last_update Tree Diagram
2.1.2.6.3.9.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null false
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.3.10 Column active
2.1.2.6.3.10.1 active Tree Diagram
2.1.2.6.3.10.2 active properties
PropertyValue
Business Name active
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.6.4 customer Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.6.5 customer Indexes
2.1.2.6.5.1 Index
Property
Namecustomer_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
customer_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.6.5.2 Index
Property
Nameidx_fk_address_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
address_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.6.5.3 Index
Property
Nameidx_fk_store_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
store_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.6.5.4 Index
Property
Nameidx_last_name
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
last_name
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.6.6 customer Triggers
2.1.2.6.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.6.7 customer JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "customer",
    "properties": {
        "customer_id": {
            "type": "number",
            "default": "nextval('customer_customer_id_seq'::regclass)"
        },
        "store_id": {
            "type": "number"
        },
        "first_name": {
            "type": "string"
        },
        "last_name": {
            "type": "string"
        },
        "email": {
            "type": "string"
        },
        "address_id": {
            "type": "number"
        },
        "activebool": {
            "type": "boolean",
            "default": true
        },
        "create_date": {
            "type": "string",
            "default": "('now'::text)::date"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        },
        "active": {
            "type": "number"
        }
    },
    "additionalProperties": true,
    "required": [
        "customer_id",
        "store_id",
        "first_name",
        "last_name",
        "address_id",
        "activebool",
        "create_date"
    ]
}
2.1.2.6.8 customer JSON data
{
    "customer_id": "nextval('customer_customer_id_seq'::regclass)",
    "store_id": -78,
    "first_name": "Lorem",
    "last_name": "Lorem",
    "email": "Lorem",
    "address_id": -92,
    "activebool": true,
    "create_date": "('now'::text)::date",
    "last_update": "now()",
    "active": -99
}
2.1.2.6.9 customer Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.customer (
	customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
	store_id smallint NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	email varchar(50),
	address_id smallint NOT NULL,
	activebool boolean DEFAULT true NOT NULL,
	create_date date DEFAULT E'(\'now\'::text)::date' NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()',
	active integer,
	CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
	CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.customer
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS customer_pkey
 ON ONLY public.customer USING BTREE (customer_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_address_id
 ON ONLY public.customer USING BTREE (address_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_store_id
 ON ONLY public.customer USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_last_name
 ON ONLY public.customer USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
2.1.2.7 Table film
2.1.2.7.1 film Tree Diagram
2.1.2.7.2 film Properties
PropertyValue
Tablefilm
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Enable TOAST autovacuum
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.7.3 film Column
ColumnTypeReqKeyDescriptionComments
film_idintegertruepk, dk
titlevarchar(255)true
descriptiontextfalse
release_yeardomainfalse
language_idsmallinttruefk
rental_durationsmallinttrue
rental_ratenumeric(4, 2)true
lengthsmallintfalse
replacement_costnumeric(5, 2)true
ratingenumfalse
last_updatetimestamptrue
special_featurestextfalse
fulltexttsvectortrue
zendeskvarchar(10)false
2.1.2.7.3.1 Column film_id
2.1.2.7.3.1.1 film_id Tree Diagram
2.1.2.7.3.1.2 film_id properties
PropertyValue
Business Name film_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('film_film_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namefilm_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.2 Column title
2.1.2.7.3.2.1 title Tree Diagram
2.1.2.7.3.2.2 title properties
PropertyValue
Business Name title
Technical name
Activated true
Id
Type char
Subtype varchar
Length 255
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.3 Column description
2.1.2.7.3.3.1 description Tree Diagram
2.1.2.7.3.3.2 description properties
PropertyValue
Business Name description
Technical name
Activated true
Id
Type char
Subtype text
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.4 Column release_year
2.1.2.7.3.4.1 release_year Tree Diagram
2.1.2.7.3.4.2 release_year properties
PropertyValue
Business Name release_year
Technical name
Activated true
Reference type model
Reference description
Lineage capture
Description
Transformation
Sources
2.1.2.7.3.5 Column language_id
2.1.2.7.3.5.1 language_id Tree Diagram
2.1.2.7.3.5.2 language_id properties
PropertyValue
Business Name language_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table language
Foreign field language_id
Relationship type Foreign Key
Relationship name film_language_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.6 Column rental_duration
2.1.2.7.3.6.1 rental_duration Tree Diagram
2.1.2.7.3.6.2 rental_duration properties
PropertyValue
Business Name rental_duration
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default 3
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.7 Column rental_rate
2.1.2.7.3.7.1 rental_rate Tree Diagram
2.1.2.7.3.7.2 rental_rate properties
PropertyValue
Business Name rental_rate
Technical name
Activated true
Id
Type numeric
Subtype numeric
Precision 4
Scale 2
Array type
Not null true
Default 4.99
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.8 Column length
2.1.2.7.3.8.1 length Tree Diagram
2.1.2.7.3.8.2 length properties
PropertyValue
Business Name length
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.9 Column replacement_cost
2.1.2.7.3.9.1 replacement_cost Tree Diagram
2.1.2.7.3.9.2 replacement_cost properties
PropertyValue
Business Name replacement_cost
Technical name
Activated true
Id
Type numeric
Subtype numeric
Precision 5
Scale 2
Array type
Not null true
Default 19.99
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.10 Column rating
2.1.2.7.3.10.1 rating Tree Diagram
2.1.2.7.3.10.2 rating properties
PropertyValue
Business Name rating
Technical name
Activated true
Reference type model
Reference description
Lineage capture
Description
Transformation
Sources
2.1.2.7.3.11 Column last_update
2.1.2.7.3.11.1 last_update Tree Diagram
2.1.2.7.3.11.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.12 Column special_features
2.1.2.7.3.12.1 special_features Tree Diagram
2.1.2.7.3.12.2 special_features properties
PropertyValue
Business Name special_features
Technical name
Activated true
Id
Type char
Subtype text
Array type
[1] Size limit
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.13 Column fulltext
2.1.2.7.3.13.1 fulltext Tree Diagram
2.1.2.7.3.13.2 fulltext properties
PropertyValue
Business Name fulltext
Technical name
Activated true
Id
Type char
Subtype tsvector
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.7.3.14 Column zendesk
2.1.2.7.3.14.1 zendesk Tree Diagram
2.1.2.7.3.14.2 zendesk properties
PropertyValue
Business Name zendesk
Technical name
Activated true
Id
Type char
Subtype varchar
Length 10
Array type
Collation rule
Not null
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources
2.1.2.7.4 film Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.7.5 film Indexes
2.1.2.7.5.1 Index
Property
Namefilm_fulltext_idx
Activatedtrue
Methodgist
Concurrent build
If not existtrue
Onlytrue
Columns
fulltext
Include non-key columns
With storage parameters
[1] Fill factor
Buffering
Tablespace
Where constraint
Comment
2.1.2.7.5.2 Index
Property
Namefilm_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
film_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.7.5.3 Index
Property
Nameidx_fk_language_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
language_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.7.5.4 Index
Property
Nameidx_title
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
title
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.7.6 film Triggers
2.1.2.7.6.1 Triggers film_fulltext_trigger
Property film_fulltext_trigger
Namefilm_fulltext_trigger
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventINSERT
[2] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functiontsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
Remarks
2.1.2.7.6.2 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.7.7 film JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "film",
    "properties": {
        "film_id": {
            "type": "number",
            "default": "nextval('film_film_id_seq'::regclass)"
        },
        "title": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "release_year": {
            "$ref": "#model/definitions/year"
        },
        "language_id": {
            "type": "number"
        },
        "rental_duration": {
            "type": "number",
            "default": "3"
        },
        "rental_rate": {
            "type": "number",
            "default": "4.99"
        },
        "length": {
            "type": "number"
        },
        "replacement_cost": {
            "type": "number",
            "default": "19.99"
        },
        "rating": {
            "$ref": "#model/definitions/mpaa_rating"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        },
        "special_features": {
            "type": "string"
        },
        "fulltext": {
            "type": "string"
        },
        "zendesk": {
            "type": "string"
        }
    },
    "additionalProperties": true,
    "required": [
        "film_id",
        "title",
        "language_id",
        "rental_duration",
        "rental_rate",
        "replacement_cost",
        "last_update",
        "fulltext"
    ]
}
2.1.2.7.8 film JSON data
{
    "film_id": "nextval('film_film_id_seq'::regclass)",
    "title": "Lorem",
    "description": "Lorem",
    "release_year": "Lorem",
    "language_id": -4,
    "rental_duration": "3",
    "rental_rate": "4.99",
    "length": 12,
    "replacement_cost": "19.99",
    "rating": "NC-17",
    "last_update": "now()",
    "special_features": "Lorem",
    "fulltext": "Lorem",
    "zendesk": "Lorem"
}
2.1.2.7.9 film Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');


CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

CREATE TABLE IF NOT EXISTS public.film (
	film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL,
	title varchar(255) NOT NULL,
	description text,
	release_year year,
	language_id smallint NOT NULL,
	rental_duration smallint DEFAULT 3 NOT NULL,
	rental_rate numeric(4,2) DEFAULT 4.99 NOT NULL,
	length smallint,
	replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL,
	rating mpaa_rating,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	special_features text[],
	fulltext tsvector NOT NULL,
	zendesk varchar(10),
	CONSTRAINT film_pkey PRIMARY KEY (film_id),
	CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES public.language (language_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER film_fulltext_trigger BEFORE INSERT OR UPDATE
	ON public.film
	FOR EACH ROW
	EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description');

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS film_fulltext_idx
 ON ONLY public.film USING GIST (fulltext pg_catalog.tsvector_ops) ;

CREATE UNIQUE INDEX IF NOT EXISTS film_pkey
 ON ONLY public.film USING BTREE (film_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_language_id
 ON ONLY public.film USING BTREE (language_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_title
 ON ONLY public.film USING BTREE (title COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
2.1.2.8 Table film_actor
2.1.2.8.1 film_actor Tree Diagram
2.1.2.8.2 film_actor Properties
PropertyValue
Tablefilm_actor
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.8.3 film_actor Column
ColumnTypeReqKeyDescriptionComments
actor_idsmallinttruepk, fk
film_idsmallinttruepk, fk
last_updatetimestamptrue
2.1.2.8.3.1 Column actor_id
2.1.2.8.3.1.1 actor_id Tree Diagram
2.1.2.8.3.1.2 actor_id properties
PropertyValue
Business Name actor_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key true
Foreign table actor
Foreign field actor_id
Relationship type Foreign Key
Relationship name film_actor_actor_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.8.3.2 Column film_id
2.1.2.8.3.2.1 film_id Tree Diagram
2.1.2.8.3.2.2 film_id properties
PropertyValue
Business Name film_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key true
Foreign table film
Foreign field film_id
Relationship type Foreign Key
Relationship name film_actor_film_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.8.3.3 Column last_update
2.1.2.8.3.3.1 last_update Tree Diagram
2.1.2.8.3.3.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.8.4 film_actor Composite keys
PropertyValue
Primary key
[1] Constraint namefilm_actor_pkey
Key
actor_id
film_id
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.8.5 film_actor Indexes
2.1.2.8.5.1 Index
Property
Namefilm_actor_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
actor_id
film_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.8.5.2 Index
Property
Nameidx_fk_film_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
film_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.8.6 film_actor Triggers
2.1.2.8.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.8.7 film_actor JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "film_actor",
    "properties": {
        "actor_id": {
            "type": "number"
        },
        "film_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "actor_id",
        "film_id",
        "last_update"
    ]
}
2.1.2.8.8 film_actor JSON data
{
    "actor_id": 58,
    "film_id": -83,
    "last_update": "now()"
}
2.1.2.8.9 film_actor Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.film_actor (
	actor_id smallint NOT NULL,
	film_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
	CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.actor (actor_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film_actor
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS film_actor_pkey
 ON ONLY public.film_actor USING BTREE (actor_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_film_id
 ON ONLY public.film_actor USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST) ;
2.1.2.9 Table film_category
2.1.2.9.1 film_category Tree Diagram
2.1.2.9.2 film_category Properties
PropertyValue
Tablefilm_category
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.9.3 film_category Column
ColumnTypeReqKeyDescriptionComments
film_idsmallinttruepk, fk
category_idsmallinttruepk, fk
last_updatetimestamptrue
2.1.2.9.3.1 Column film_id
2.1.2.9.3.1.1 film_id Tree Diagram
2.1.2.9.3.1.2 film_id properties
PropertyValue
Business Name film_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key true
Foreign table film
Foreign field film_id
Relationship type Foreign Key
Relationship name film_category_film_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.9.3.2 Column category_id
2.1.2.9.3.2.1 category_id Tree Diagram
2.1.2.9.3.2.2 category_id properties
PropertyValue
Business Name category_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key true
Foreign table category
Foreign field category_id
Relationship type Foreign Key
Relationship name film_category_category_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.9.3.3 Column last_update
2.1.2.9.3.3.1 last_update Tree Diagram
2.1.2.9.3.3.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.9.4 film_category Composite keys
PropertyValue
Primary key
[1] Constraint namefilm_category_pkey
Key
film_id
category_id
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.9.5 film_category Indexes
2.1.2.9.5.1 Index
Property
Namefilm_category_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
film_id
category_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.9.6 film_category Triggers
2.1.2.9.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.9.7 film_category JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "film_category",
    "properties": {
        "film_id": {
            "type": "number"
        },
        "category_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "film_id",
        "category_id",
        "last_update"
    ]
}
2.1.2.9.8 film_category JSON data
{
    "film_id": -10,
    "category_id": 32,
    "last_update": "now()"
}
2.1.2.9.9 film_category Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.film_category (
	film_id smallint NOT NULL,
	category_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
	CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category (category_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film_category
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS film_category_pkey
 ON ONLY public.film_category USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST, category_id pg_catalog.int2_ops ASC NULLS LAST) ;
2.1.2.10 Table inventory
2.1.2.10.1 inventory Tree Diagram
2.1.2.10.2 inventory Properties
PropertyValue
Tableinventory
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.10.3 inventory Column
ColumnTypeReqKeyDescriptionComments
inventory_idintegertruepk, dk
film_idsmallinttruefk
store_idsmallinttrue
last_updatetimestamptrue
2.1.2.10.3.1 Column inventory_id
2.1.2.10.3.1.1 inventory_id Tree Diagram
2.1.2.10.3.1.2 inventory_id properties
PropertyValue
Business Name inventory_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('inventory_inventory_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint nameinventory_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.10.3.2 Column film_id
2.1.2.10.3.2.1 film_id Tree Diagram
2.1.2.10.3.2.2 film_id properties
PropertyValue
Business Name film_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table film
Foreign field film_id
Relationship type Foreign Key
Relationship name inventory_film_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.10.3.3 Column store_id
2.1.2.10.3.3.1 store_id Tree Diagram
2.1.2.10.3.3.2 store_id properties
PropertyValue
Business Name store_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.10.3.4 Column last_update
2.1.2.10.3.4.1 last_update Tree Diagram
2.1.2.10.3.4.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.10.4 inventory Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.10.5 inventory Indexes
2.1.2.10.5.1 Index
Property
Nameidx_store_id_film_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
store_id
film_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.10.5.2 Index
Property
Nameinventory_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
inventory_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.10.6 inventory Triggers
2.1.2.10.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.10.7 inventory JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "inventory",
    "properties": {
        "inventory_id": {
            "type": "number",
            "default": "nextval('inventory_inventory_id_seq'::regclass)"
        },
        "film_id": {
            "type": "number"
        },
        "store_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "inventory_id",
        "film_id",
        "store_id",
        "last_update"
    ]
}
2.1.2.10.8 inventory JSON data
{
    "inventory_id": "nextval('inventory_inventory_id_seq'::regclass)",
    "film_id": -12,
    "store_id": 81,
    "last_update": "now()"
}
2.1.2.10.9 inventory Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.inventory (
	inventory_id integer DEFAULT nextval('inventory_inventory_id_seq'::regclass) NOT NULL,
	film_id smallint NOT NULL,
	store_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
	CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.inventory
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS idx_store_id_film_id
 ON ONLY public.inventory USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS inventory_pkey
 ON ONLY public.inventory USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.11 Table language
2.1.2.11.1 language Tree Diagram
2.1.2.11.2 language Properties
PropertyValue
Tablelanguage
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.11.3 language Column
ColumnTypeReqKeyDescriptionComments
language_idintegertruepk, dk
namechar(20)true
last_updatetimestamptrue
2.1.2.11.3.1 Column language_id
2.1.2.11.3.1.1 language_id Tree Diagram
2.1.2.11.3.1.2 language_id properties
PropertyValue
Business Name language_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('language_language_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namelanguage_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.11.3.2 Column name
2.1.2.11.3.2.1 name Tree Diagram
2.1.2.11.3.2.2 name properties
PropertyValue
Business Name name
Technical name
Activated true
Id
Type char
Subtype char
Length 20
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.11.3.3 Column last_update
2.1.2.11.3.3.1 last_update Tree Diagram
2.1.2.11.3.3.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.11.4 language Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.11.5 language Indexes
2.1.2.11.5.1 Index
Property
Namelanguage_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
language_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.11.6 language Triggers
2.1.2.11.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.11.7 language JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "language",
    "properties": {
        "language_id": {
            "type": "number",
            "default": "nextval('language_language_id_seq'::regclass)"
        },
        "name": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "language_id",
        "name",
        "last_update"
    ]
}
2.1.2.11.8 language JSON data
{
    "language_id": "nextval('language_language_id_seq'::regclass)",
    "name": "Lorem",
    "last_update": "now()"
}
2.1.2.11.9 language Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.language (
	language_id integer DEFAULT nextval('language_language_id_seq'::regclass) NOT NULL,
	name char(20) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT language_pkey PRIMARY KEY (language_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.language
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS language_pkey
 ON ONLY public.language USING BTREE (language_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.12 Table payment
2.1.2.12.1 payment Tree Diagram
2.1.2.12.2 payment Properties
PropertyValue
Tablepayment
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.12.3 payment Column
ColumnTypeReqKeyDescriptionComments
payment_idintegertruepk
customer_idsmallinttruefk
staff_idsmallinttruefk
rental_idintegertruefk
amountnumeric(5, 2)true
payment_datetimestamptrue
2.1.2.12.3.1 Column payment_id
2.1.2.12.3.1.1 payment_id Tree Diagram
2.1.2.12.3.1.2 payment_id properties
PropertyValue
Business Name payment_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('payment_payment_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namepayment_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.3.2 Column customer_id
2.1.2.12.3.2.1 customer_id Tree Diagram
2.1.2.12.3.2.2 customer_id properties
PropertyValue
Business Name customer_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table customer
Foreign field customer_id
Relationship type Foreign Key
Relationship name payment_customer_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.3.3 Column staff_id
2.1.2.12.3.3.1 staff_id Tree Diagram
2.1.2.12.3.3.2 staff_id properties
PropertyValue
Business Name staff_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table staff
Foreign field staff_id
Relationship type Foreign Key
Relationship name payment_staff_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.3.4 Column rental_id
2.1.2.12.3.4.1 rental_id Tree Diagram
2.1.2.12.3.4.2 rental_id properties
PropertyValue
Business Name rental_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table rental
Foreign field rental_id
Relationship type Foreign Key
Relationship name payment_rental_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.3.5 Column amount
2.1.2.12.3.5.1 amount Tree Diagram
2.1.2.12.3.5.2 amount properties
PropertyValue
Business Name amount
Technical name
Activated true
Id
Type numeric
Subtype numeric
Precision 5
Scale 2
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.3.6 Column payment_date
2.1.2.12.3.6.1 payment_date Tree Diagram
2.1.2.12.3.6.2 payment_date properties
PropertyValue
Business Name payment_date
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.12.4 payment Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.12.5 payment Indexes
2.1.2.12.5.1 Index
Property
Nameidx_fk_customer_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
customer_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.12.5.2 Index
Property
Nameidx_fk_rental_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
rental_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.12.5.3 Index
Property
Nameidx_fk_staff_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
staff_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.12.5.4 Index
Property
Namepayment_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
payment_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.12.6 payment JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "payment",
    "properties": {
        "payment_id": {
            "type": "number",
            "default": "nextval('payment_payment_id_seq'::regclass)"
        },
        "customer_id": {
            "type": "number"
        },
        "staff_id": {
            "type": "number"
        },
        "rental_id": {
            "type": "number"
        },
        "amount": {
            "type": "number"
        },
        "payment_date": {
            "type": "string"
        }
    },
    "additionalProperties": true,
    "required": [
        "payment_id",
        "customer_id",
        "staff_id",
        "rental_id",
        "amount",
        "payment_date"
    ]
}
2.1.2.12.7 payment JSON data
{
    "payment_id": "nextval('payment_payment_id_seq'::regclass)",
    "customer_id": -51,
    "staff_id": 84,
    "rental_id": 9,
    "amount": -14,
    "payment_date": "2011-02-03 04:05:00+0000"
}
2.1.2.12.8 payment Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.payment (
	payment_id integer DEFAULT nextval('payment_payment_id_seq'::regclass) NOT NULL,
	customer_id smallint NOT NULL,
	staff_id smallint NOT NULL,
	rental_id integer NOT NULL,
	amount numeric(5,2) NOT NULL,
	payment_date timestamp WITHOUT TIME ZONE NOT NULL,
	CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
	CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES public.rental (rental_id) MATCH SIMPLE ON DELETE SET NULL ON UPDATE CASCADE,
	CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_fk_customer_id
 ON ONLY public.payment USING BTREE (customer_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_rental_id
 ON ONLY public.payment USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_staff_id
 ON ONLY public.payment USING BTREE (staff_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS payment_pkey
 ON ONLY public.payment USING BTREE (payment_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.13 Table rental
2.1.2.13.1 rental Tree Diagram
2.1.2.13.2 rental Properties
PropertyValue
Tablerental
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.13.3 rental Column
ColumnTypeReqKeyDescriptionComments
rental_idintegertruepk, dk
rental_datetimestamptrue
inventory_idintegertruefk
customer_idsmallinttruefk
return_datetimestampfalse
staff_idsmallinttruefk
last_updatetimestamptrue
2.1.2.13.3.1 Column rental_id
2.1.2.13.3.1.1 rental_id Tree Diagram
2.1.2.13.3.1.2 rental_id properties
PropertyValue
Business Name rental_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('rental_rental_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namerental_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.2 Column rental_date
2.1.2.13.3.2.1 rental_date Tree Diagram
2.1.2.13.3.2.2 rental_date properties
PropertyValue
Business Name rental_date
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.3 Column inventory_id
2.1.2.13.3.3.1 inventory_id Tree Diagram
2.1.2.13.3.3.2 inventory_id properties
PropertyValue
Business Name inventory_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table inventory
Foreign field inventory_id
Relationship type Foreign Key
Relationship name rental_inventory_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.4 Column customer_id
2.1.2.13.3.4.1 customer_id Tree Diagram
2.1.2.13.3.4.2 customer_id properties
PropertyValue
Business Name customer_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table customer
Foreign field customer_id
Relationship type Foreign Key
Relationship name rental_customer_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.5 Column return_date
2.1.2.13.3.5.1 return_date Tree Diagram
2.1.2.13.3.5.2 return_date properties
PropertyValue
Business Name return_date
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null false
Default
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.6 Column staff_id
2.1.2.13.3.6.1 staff_id Tree Diagram
2.1.2.13.3.6.2 staff_id properties
PropertyValue
Business Name staff_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table staff
Foreign field staff_id
Relationship type Foreign Key
Relationship name rental_staff_id_key
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.3.7 Column last_update
2.1.2.13.3.7.1 last_update Tree Diagram
2.1.2.13.3.7.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.13.4 rental Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.13.5 rental Indexes
2.1.2.13.5.1 Index
Property
Nameidx_fk_inventory_id
Activatedtrue
Methodbtree
Unique
Concurrent build
If not existtrue
Onlytrue
Columns
inventory_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.13.5.2 Index
Property
Nameidx_unq_rental_rental_date_inventory_id_customer_id
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
rental_date
inventory_id
customer_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.13.5.3 Index
Property
Namerental_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
rental_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.13.6 rental Triggers
2.1.2.13.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.13.7 rental JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "rental",
    "properties": {
        "rental_id": {
            "type": "number",
            "default": "nextval('rental_rental_id_seq'::regclass)"
        },
        "rental_date": {
            "type": "string"
        },
        "inventory_id": {
            "type": "number"
        },
        "customer_id": {
            "type": "number"
        },
        "return_date": {
            "type": "string"
        },
        "staff_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "rental_id",
        "rental_date",
        "inventory_id",
        "customer_id",
        "staff_id",
        "last_update"
    ]
}
2.1.2.13.8 rental JSON data
{
    "rental_id": "nextval('rental_rental_id_seq'::regclass)",
    "rental_date": "2011-02-03 04:05:00+0000",
    "inventory_id": 57,
    "customer_id": -58,
    "return_date": "2011-02-03 04:05:00+0000",
    "staff_id": -63,
    "last_update": "now()"
}
2.1.2.13.9 rental Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.rental (
	rental_id integer DEFAULT nextval('rental_rental_id_seq'::regclass) NOT NULL,
	rental_date timestamp WITHOUT TIME ZONE NOT NULL,
	inventory_id integer NOT NULL,
	customer_id smallint NOT NULL,
	return_date timestamp WITHOUT TIME ZONE,
	staff_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
	CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES public.inventory (inventory_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.rental
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS idx_fk_inventory_id
 ON ONLY public.rental USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_rental_rental_date_inventory_id_customer_id
 ON ONLY public.rental USING BTREE (rental_date pg_catalog.timestamp_ops ASC NULLS LAST, inventory_id pg_catalog.int4_ops ASC NULLS LAST, customer_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS rental_pkey
 ON ONLY public.rental USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.14 Table staff
2.1.2.14.1 staff Tree Diagram
2.1.2.14.2 staff Properties
PropertyValue
Tablestaff
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Enable TOAST autovacuum
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.14.3 staff Column
ColumnTypeReqKeyDescriptionComments
staff_idintegertruepk, dk
first_namevarchar(45)true
last_namevarchar(45)true
address_idsmallinttruefk
emailvarchar(50)false
store_idsmallinttrue
activebooleantrue
usernamevarchar(16)true
passwordvarchar(40)false
last_updatetimestamptrue
picturebyteafalse
2.1.2.14.3.1 Column staff_id
2.1.2.14.3.1.1 staff_id Tree Diagram
2.1.2.14.3.1.2 staff_id properties
PropertyValue
Business Name staff_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('staff_staff_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namestaff_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.2 Column first_name
2.1.2.14.3.2.1 first_name Tree Diagram
2.1.2.14.3.2.2 first_name properties
PropertyValue
Business Name first_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.3 Column last_name
2.1.2.14.3.3.1 last_name Tree Diagram
2.1.2.14.3.3.2 last_name properties
PropertyValue
Business Name last_name
Technical name
Activated true
Id
Type char
Subtype varchar
Length 45
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.4 Column address_id
2.1.2.14.3.4.1 address_id Tree Diagram
2.1.2.14.3.4.2 address_id properties
PropertyValue
Business Name address_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table address
Foreign field address_id
Relationship type Foreign Key
Relationship name staff_address_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.5 Column email
2.1.2.14.3.5.1 email Tree Diagram
2.1.2.14.3.5.2 email properties
PropertyValue
Business Name email
Technical name
Activated true
Id
Type char
Subtype varchar
Length 50
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.6 Column store_id
2.1.2.14.3.6.1 store_id Tree Diagram
2.1.2.14.3.6.2 store_id properties
PropertyValue
Business Name store_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.7 Column active
2.1.2.14.3.7.1 active Tree Diagram
2.1.2.14.3.7.2 active properties
PropertyValue
Business Name active
Technical name
Activated true
Id
Type boolean
Comments
Array type
Not null true
Default true
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.8 Column username
2.1.2.14.3.8.1 username Tree Diagram
2.1.2.14.3.8.2 username properties
PropertyValue
Business Name username
Technical name
Activated true
Id
Type char
Subtype varchar
Length 16
Array type
Collation rule
Not null true
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.9 Column password
2.1.2.14.3.9.1 password Tree Diagram
2.1.2.14.3.9.2 password properties
PropertyValue
Business Name password
Technical name
Activated true
Id
Type char
Subtype varchar
Length 40
Array type
Collation rule
Not null false
Default
Comments
Primary key false
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Max length
Pattern
Format
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.10 Column last_update
2.1.2.14.3.10.1 last_update Tree Diagram
2.1.2.14.3.10.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.3.11 Column picture
2.1.2.14.3.11.1 picture Tree Diagram
2.1.2.14.3.11.2 picture properties
PropertyValue
Business Name picture
Technical name
Activated true
Id
Type binary
Subtype bytea
Comments
Array type
Not null false
Primary key false
Unique false
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.14.4 staff Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.14.5 staff Indexes
2.1.2.14.5.1 Index
Property
Namestaff_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
staff_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.14.6 staff Triggers
2.1.2.14.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.14.7 staff JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "staff",
    "properties": {
        "staff_id": {
            "type": "number",
            "default": "nextval('staff_staff_id_seq'::regclass)"
        },
        "first_name": {
            "type": "string"
        },
        "last_name": {
            "type": "string"
        },
        "address_id": {
            "type": "number"
        },
        "email": {
            "type": "string"
        },
        "store_id": {
            "type": "number"
        },
        "active": {
            "type": "boolean",
            "default": true
        },
        "username": {
            "type": "string"
        },
        "password": {
            "type": "string"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        },
        "picture": {
            "type": "string"
        }
    },
    "additionalProperties": true,
    "required": [
        "staff_id",
        "first_name",
        "last_name",
        "address_id",
        "store_id",
        "active",
        "username",
        "last_update"
    ]
}
2.1.2.14.8 staff JSON data
{
    "staff_id": "nextval('staff_staff_id_seq'::regclass)",
    "first_name": "Lorem",
    "last_name": "Lorem",
    "address_id": 56,
    "email": "Lorem",
    "store_id": -82,
    "active": true,
    "username": "Lorem",
    "password": "Lorem",
    "last_update": "now()"
}
2.1.2.14.9 staff Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.staff (
	staff_id integer DEFAULT nextval('staff_staff_id_seq'::regclass) NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	address_id smallint NOT NULL,
	email varchar(50),
	store_id smallint NOT NULL,
	active boolean DEFAULT true NOT NULL,
	username varchar(16) NOT NULL,
	password varchar(40),
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	picture bytea,
	CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
	CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.staff
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS staff_pkey
 ON ONLY public.staff USING BTREE (staff_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.2.15 Table store
2.1.2.15.1 store Tree Diagram
2.1.2.15.2 store Properties
PropertyValue
Tablestore
Technical name
Activatedtrue
Id
Schemapublic
Additional propertiestrue
$ref
$ref
Comments
Temporaryfalse
Unloggedfalse
If not existstrue
Inherits parent tables
[1] Table name
Partition of
Partition bounds
Partitioning
[1] Partition method
Partition by
Partition key
Expression
Using method
Storage parameters
[1] Fill factor
Parallel workers
Enable autovacuum
Autovacuum params
Enable TOAST autovacuum
TOAST parameters
User catalog table
Tablespacepg_default
As Select statement
Remarks
2.1.2.15.3 store Column
ColumnTypeReqKeyDescriptionComments
store_idintegertruepk
manager_staff_idsmallinttruefk
address_idsmallinttruefk
last_updatetimestamptrue
2.1.2.15.3.1 Column store_id
2.1.2.15.3.1.1 store_id Tree Diagram
2.1.2.15.3.1.2 store_id properties
PropertyValue
Business Name store_id
Technical name
Activated true
Id
Type numeric
Subtype integer
Array type
Not null true
Default nextval('store_store_id_seq'::regclass)
Comments
Primary key true
Primary key options
[1] Constraint namestore_pkey
Include non-key columns
With storage parameters
Tablespace
Comment
Unique false
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.15.3.2 Column manager_staff_id
2.1.2.15.3.2.1 manager_staff_id Tree Diagram
2.1.2.15.3.2.2 manager_staff_id properties
PropertyValue
Business Name manager_staff_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table staff
Foreign field staff_id
Relationship type Foreign Key
Relationship name store_manager_staff_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.15.3.3 Column address_id
2.1.2.15.3.3.1 address_id Tree Diagram
2.1.2.15.3.3.2 address_id properties
PropertyValue
Business Name address_id
Technical name
Activated true
Id
Type numeric
Subtype smallint
Array type
Not null true
Default
Comments
Primary key false
Unique false
Foreign table address
Foreign field address_id
Relationship type Foreign Key
Relationship name store_address_id_fkey
Cardinality 0..n
Unit
Min value
Excl min false
Max value
Excl max false
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.15.3.4 Column last_update
2.1.2.15.3.4.1 last_update Tree Diagram
2.1.2.15.3.4.2 last_update properties
PropertyValue
Business Name last_update
Technical name
Activated true
Id
Type datetime
Subtype timestamp
Precision
Timezone WITHOUT TIME ZONE
Comments
Array type
Not null true
Default now()
Primary key false
Unique false
Pattern
Enum
Faker function
Sample
Remarks
Lineage capture
Description
Transformation
Sources [object Object]
2.1.2.15.4 store Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Unique key
[1] Constraint name
Key
Include non-key columns
With storage parameters
Index tablespace
Comment
Nulls Distinct
2.1.2.15.5 store Indexes
2.1.2.15.5.1 Index
Property
Nameidx_unq_manager_staff_id
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
manager_staff_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.15.5.2 Index
Property
Namestore_pkey
Activatedtrue
Methodbtree
Uniquetrue
Nulls Distinct
Concurrent build
If not existtrue
Onlytrue
Columns
store_id
Include non-key columns
With storage parameters
[1] Fill factor
Deduplicate items
Tablespace
Where constraint
Comment
2.1.2.15.6 store Triggers
2.1.2.15.6.1 Triggers last_updated
Property last_updated
Namelast_updated
Description
Or replace
Constraint
Trigger typeBEFORE
Trigger events
[1] EventUPDATE
Update columns
Referencing
Trigger for each row/statementFOR EACH ROW
Trigger WHEN condition
Functionlast_updated()
Remarks
2.1.2.15.7 store JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "store",
    "properties": {
        "store_id": {
            "type": "number",
            "default": "nextval('store_store_id_seq'::regclass)"
        },
        "manager_staff_id": {
            "type": "number"
        },
        "address_id": {
            "type": "number"
        },
        "last_update": {
            "type": "string",
            "default": "now()"
        }
    },
    "additionalProperties": true,
    "required": [
        "store_id",
        "manager_staff_id",
        "address_id",
        "last_update"
    ]
}
2.1.2.15.8 store JSON data
{
    "store_id": "nextval('store_store_id_seq'::regclass)",
    "manager_staff_id": -96,
    "address_id": -68,
    "last_update": "now()"
}
2.1.2.15.9 store Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

 */

/*
CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

 */

CREATE TABLE IF NOT EXISTS public.store (
	store_id integer DEFAULT nextval('store_store_id_seq'::regclass) NOT NULL,
	manager_staff_id smallint NOT NULL,
	address_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT store_pkey PRIMARY KEY (store_id),
	CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.store
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_manager_staff_id
 ON ONLY public.store USING BTREE (manager_staff_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS store_pkey
 ON ONLY public.store USING BTREE (store_id pg_catalog.int4_ops ASC NULLS LAST) ;
2.1.3 public Functions
2.1.3.1 Functions _group_concat
Property _group_concat
Name_group_concat
Comments
Or replace
Arguments
[1] Arg modeIN
Argument name
Argument typetext
Default expression
[2] Arg modeIN
Argument name
Argument typetext
Default expression
Returns set of
Returns data typetext
Languagesql
Definition SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 || ', ' || $2 END
VolatilityIMMUTABLE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.2 Functions film_in_stock
Property film_in_stock
Namefilm_in_stock
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namep_film_id
Argument typeinteger
Default expression
[2] Arg modeIN
Argument namep_store_id
Argument typeinteger
Default expression
[3] Arg modeOUT
Argument namep_film_count
Argument typeinteger
Default expression
Returns set oftrue
Returns data typeint4
Languagesql
Definition SELECT inventory_id FROM inventory WHERE film_id = $1 AND store_id = $2 AND inventory_in_stock(inventory_id);
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Estimated rows
Support function
Config parameters
Remarks
2.1.3.3 Functions film_not_in_stock
Property film_not_in_stock
Namefilm_not_in_stock
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namep_film_id
Argument typeinteger
Default expression
[2] Arg modeIN
Argument namep_store_id
Argument typeinteger
Default expression
[3] Arg modeOUT
Argument namep_film_count
Argument typeinteger
Default expression
Returns set oftrue
Returns data typeint4
Languagesql
Definition SELECT inventory_id FROM inventory WHERE film_id = $1 AND store_id = $2 AND NOT inventory_in_stock(inventory_id);
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Estimated rows
Support function
Config parameters
Remarks
2.1.3.4 Functions get_customer_balance
Property get_customer_balance
Nameget_customer_balance
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namep_customer_id
Argument typeinteger
Default expression
[2] Arg modeIN
Argument namep_effective_date
Argument typetimestamp without time zone
Default expression
Returns set of
Returns data typenumeric
Languageplpgsql
Definition --#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE --#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS: --# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS --# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE --# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST --# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED DECLARE v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY v_overfees INTEGER; --#LATE FEES FOR PRIOR RENTALS v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY BEGIN SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval), ((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(payment.amount),0) INTO v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id; RETURN v_rentfees + v_overfees - v_payments; END
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.5 Functions inventory_held_by_customer
Property inventory_held_by_customer
Nameinventory_held_by_customer
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namep_inventory_id
Argument typeinteger
Default expression
Returns set of
Returns data typeint4
Languageplpgsql
Definition DECLARE v_customer_id INTEGER; BEGIN SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.6 Functions inventory_in_stock
Property inventory_in_stock
Nameinventory_in_stock
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namep_inventory_id
Argument typeinteger
Default expression
Returns set of
Returns data typebool
Languageplpgsql
Definition DECLARE v_rentals INTEGER; v_out INTEGER; BEGIN -- AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE -- FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED SELECT count(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id; IF v_rentals = 0 THEN RETURN TRUE; END IF; SELECT COUNT(rental_id) INTO v_out FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL; IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.7 Functions last_day
Property last_day
Namelast_day
Comments
Or replace
Arguments
[1] Arg modeIN
Argument name
Argument typetimestamp without time zone
Default expression
Returns set of
Returns data typedate
Languagesql
Definition SELECT CASE WHEN EXTRACT(MONTH FROM $1) = 12 THEN (((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date ELSE ((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date END
VolatilityIMMUTABLE
Leak proof
When NULL argsSTRICT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.8 Functions last_updated
Property last_updated
Namelast_updated
Comments
Or replace
Arguments
[1] Arg mode
Argument name
Argument type
Default expression
Returns set of
Returns data typetrigger
Languageplpgsql
Definition BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityINVOKER
Parallel
Estimated cost
Support function
Config parameters
Remarks
2.1.3.9 Functions rewards_report
Property rewards_report
Namerewards_report
Comments
Or replace
Arguments
[1] Arg modeIN
Argument namemin_monthly_purchases
Argument typeinteger
Default expression
[2] Arg modeIN
Argument namemin_dollar_amount_purchased
Argument typenumeric
Default expression
Returns set oftrue
Returns data typecustomer
Languageplpgsql
Definition DECLARE last_month_start DATE; last_month_end DATE; rr RECORD; tmpSQL TEXT; BEGIN /* Some sanity checks... */ IF min_monthly_purchases = 0 THEN RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0'; END IF; IF min_dollar_amount_purchased = 0.00 THEN RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00'; END IF; last_month_start := CURRENT_DATE - '3 month'::interval; last_month_start := to_date((extract(YEAR FROM last_month_start) || '-' || extract(MONTH FROM last_month_start) || '-01'),'YYYY-MM-DD'); last_month_end := LAST_DAY(last_month_start); /* Create a temporary storage area for Customer IDs. */ CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY); /* Find all customers meeting the monthly purchase requirements */ tmpSQL := 'INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN '||quote_literal(last_month_start) ||' AND '|| quote_literal(last_month_end) || ' GROUP BY customer_id HAVING SUM(p.amount) > '|| min_dollar_amount_purchased || ' AND COUNT(customer_id) > ' ||min_monthly_purchases ; EXECUTE tmpSQL; /* Output ALL customer information of matching rewardees. Customize output as needed. */ FOR rr IN EXECUTE 'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP RETURN NEXT rr; END LOOP; /* Clean up */ tmpSQL := 'DROP TABLE tmpCustomer'; EXECUTE tmpSQL; RETURN; END
VolatilityVOLATILE
Leak proof
When NULL argsCALLED ON NULL INPUT
SQL SecurityDEFINER
Parallel
Estimated cost
Estimated rows
Support function
Config parameters
Remarks
2.1.4 public Target Script
CREATE DATABASE dvdrental
	ENCODING UTF8
	LC_COLLATE 'en_US.UTF-8'
	LC_CTYPE 'en_US.UTF-8'
	TABLESPACE 'pg_default';

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

CREATE FUNCTION public._group_concat
	(IN  text, IN  text)
	RETURNS text
	LANGUAGE sql
	IMMUTABLE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

SELECT CASE
  WHEN $2 IS NULL THEN $1
  WHEN $1 IS NULL THEN $2
  ELSE $1 || ', ' || $2
END

$BODY$;

CREATE FUNCTION public.film_in_stock
	(IN p_film_id integer, IN p_store_id integer, OUT p_film_count integer)
	RETURNS SETOF int4
	LANGUAGE sql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

     SELECT inventory_id
     FROM inventory
     WHERE film_id = $1
     AND store_id = $2
     AND inventory_in_stock(inventory_id);

$BODY$;

CREATE FUNCTION public.film_not_in_stock
	(IN p_film_id integer, IN p_store_id integer, OUT p_film_count integer)
	RETURNS SETOF int4
	LANGUAGE sql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

    SELECT inventory_id
    FROM inventory
    WHERE film_id = $1
    AND store_id = $2
    AND NOT inventory_in_stock(inventory_id);

$BODY$;

CREATE FUNCTION public.get_customer_balance
	(IN p_customer_id integer, IN p_effective_date timestamp without time zone)
	RETURNS numeric
	LANGUAGE plpgsql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

       --#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
       --#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
       --#   1) RENTAL FEES FOR ALL PREVIOUS RENTALS
       --#   2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
       --#   3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
       --#   4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
DECLARE
    v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY
    v_overfees INTEGER;      --#LATE FEES FOR PRIOR RENTALS
    v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY
BEGIN
    SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

    SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval),
        ((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0)),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

    SELECT COALESCE(SUM(payment.amount),0) INTO v_payments
    FROM payment
    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

    RETURN v_rentfees + v_overfees - v_payments;
END

$BODY$;

CREATE FUNCTION public.inventory_held_by_customer
	(IN p_inventory_id integer)
	RETURNS int4
	LANGUAGE plpgsql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

DECLARE
    v_customer_id INTEGER;
BEGIN

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END 
$BODY$;

CREATE FUNCTION public.inventory_in_stock
	(IN p_inventory_id integer)
	RETURNS bool
	LANGUAGE plpgsql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

DECLARE
    v_rentals INTEGER;
    v_out     INTEGER;
BEGIN
    -- AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    -- FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

    SELECT count(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END 
$BODY$;

CREATE FUNCTION public.last_day
	(IN  timestamp without time zone)
	RETURNS date
	LANGUAGE sql
	IMMUTABLE
	NOT LEAKPROOF
	STRICT
	SECURITY INVOKER
AS $BODY$

  SELECT CASE
    WHEN EXTRACT(MONTH FROM $1) = 12 THEN
      (((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date
    ELSE
      ((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date
    END

$BODY$;

CREATE FUNCTION public.last_updated
	()
	RETURNS trigger
	LANGUAGE plpgsql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY INVOKER
AS $BODY$

BEGIN
    NEW.last_update = CURRENT_TIMESTAMP;
    RETURN NEW;
END 
$BODY$;

CREATE FUNCTION public.rewards_report
	(IN min_monthly_purchases integer, IN min_dollar_amount_purchased numeric)
	RETURNS SETOF customer
	LANGUAGE plpgsql
	VOLATILE
	NOT LEAKPROOF
	CALLED ON NULL INPUT
	SECURITY DEFINER
AS $BODY$

DECLARE
    last_month_start DATE;
    last_month_end DATE;
rr RECORD;
tmpSQL TEXT;
BEGIN

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
        RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0';
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00';
    END IF;

    last_month_start := CURRENT_DATE - '3 month'::interval;
    last_month_start := to_date((extract(YEAR FROM last_month_start) || '-' || extract(MONTH FROM last_month_start) || '-01'),'YYYY-MM-DD');
    last_month_end := LAST_DAY(last_month_start);

    /*
    Create a temporary storage area for Customer IDs.
    */
    CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY);

    /*
    Find all customers meeting the monthly purchase requirements
    */

    tmpSQL := 'INSERT INTO tmpCustomer (customer_id)
        SELECT p.customer_id
        FROM payment AS p
        WHERE DATE(p.payment_date) BETWEEN '||quote_literal(last_month_start) ||' AND '|| quote_literal(last_month_end) || '
        GROUP BY customer_id
        HAVING SUM(p.amount) > '|| min_dollar_amount_purchased || '
        AND COUNT(customer_id) > ' ||min_monthly_purchases ;

    EXECUTE tmpSQL;

    /*
    Output ALL customer information of matching rewardees.
    Customize output as needed.
    */
    FOR rr IN EXECUTE 'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP
        RETURN NEXT rr;
    END LOOP;

    /* Clean up */
    tmpSQL := 'DROP TABLE tmpCustomer';
    EXECUTE tmpSQL;

RETURN;
END

$BODY$;

CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');


CREATE DOMAIN public.year AS integer
	CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

CREATE TABLE IF NOT EXISTS public.actor (
	actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.actor
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS actor_pkey
 ON ONLY public.actor USING BTREE (actor_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_actor_last_name
 ON ONLY public.actor USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.country (
	country_id integer DEFAULT nextval('country_country_id_seq'::regclass) NOT NULL,
	country varchar(50) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT country_pkey PRIMARY KEY (country_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.country
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS country_pkey
 ON ONLY public.country USING BTREE (country_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.city (
	city_id integer DEFAULT nextval('city_city_id_seq'::regclass) NOT NULL,
	city varchar(50) NOT NULL,
	country_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT city_pkey PRIMARY KEY (city_id),
	CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES public.country (country_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.city
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS city_pkey
 ON ONLY public.city USING BTREE (city_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_country_id
 ON ONLY public.city USING BTREE (country_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.address (
	address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL,
	address varchar(50) NOT NULL,
	address2 varchar(50),
	district varchar(20) NOT NULL,
	city_id smallint NOT NULL,
	postal_code varchar(10),
	phone varchar(20) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT address_pkey PRIMARY KEY (address_id),
	CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city (city_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.address
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS address_pkey
 ON ONLY public.address USING BTREE (address_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_city_id
 ON ONLY public.address USING BTREE (city_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.category (
	category_id integer DEFAULT nextval('category_category_id_seq'::regclass) NOT NULL,
	name varchar(25) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT category_pkey PRIMARY KEY (category_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.category
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS category_pkey
 ON ONLY public.category USING BTREE (category_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.customer (
	customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
	store_id smallint NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	email varchar(50),
	address_id smallint NOT NULL,
	activebool boolean DEFAULT true NOT NULL,
	create_date date DEFAULT E'(\'now\'::text)::date' NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()',
	active integer,
	CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
	CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.customer
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS customer_pkey
 ON ONLY public.customer USING BTREE (customer_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_address_id
 ON ONLY public.customer USING BTREE (address_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_store_id
 ON ONLY public.customer USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_last_name
 ON ONLY public.customer USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.language (
	language_id integer DEFAULT nextval('language_language_id_seq'::regclass) NOT NULL,
	name char(20) NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT language_pkey PRIMARY KEY (language_id)
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.language
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS language_pkey
 ON ONLY public.language USING BTREE (language_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.film (
	film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL,
	title varchar(255) NOT NULL,
	description text,
	release_year year,
	language_id smallint NOT NULL,
	rental_duration smallint DEFAULT 3 NOT NULL,
	rental_rate numeric(4,2) DEFAULT 4.99 NOT NULL,
	length smallint,
	replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL,
	rating mpaa_rating,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	special_features text[],
	fulltext tsvector NOT NULL,
	zendesk varchar(10),
	CONSTRAINT film_pkey PRIMARY KEY (film_id),
	CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES public.language (language_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER film_fulltext_trigger BEFORE INSERT OR UPDATE
	ON public.film
	FOR EACH ROW
	EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description');

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS film_fulltext_idx
 ON ONLY public.film USING GIST (fulltext pg_catalog.tsvector_ops) ;

CREATE UNIQUE INDEX IF NOT EXISTS film_pkey
 ON ONLY public.film USING BTREE (film_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_language_id
 ON ONLY public.film USING BTREE (language_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_title
 ON ONLY public.film USING BTREE (title COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.film_actor (
	actor_id smallint NOT NULL,
	film_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
	CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.actor (actor_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film_actor
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS film_actor_pkey
 ON ONLY public.film_actor USING BTREE (actor_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_film_id
 ON ONLY public.film_actor USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.film_category (
	film_id smallint NOT NULL,
	category_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
	CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category (category_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.film_category
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS film_category_pkey
 ON ONLY public.film_category USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST, category_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.inventory (
	inventory_id integer DEFAULT nextval('inventory_inventory_id_seq'::regclass) NOT NULL,
	film_id smallint NOT NULL,
	store_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
	CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.inventory
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS idx_store_id_film_id
 ON ONLY public.inventory USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS inventory_pkey
 ON ONLY public.inventory USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.staff (
	staff_id integer DEFAULT nextval('staff_staff_id_seq'::regclass) NOT NULL,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	address_id smallint NOT NULL,
	email varchar(50),
	store_id smallint NOT NULL,
	active boolean DEFAULT true NOT NULL,
	username varchar(16) NOT NULL,
	password varchar(40),
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	picture bytea,
	CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
	CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.staff
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS staff_pkey
 ON ONLY public.staff USING BTREE (staff_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.rental (
	rental_id integer DEFAULT nextval('rental_rental_id_seq'::regclass) NOT NULL,
	rental_date timestamp WITHOUT TIME ZONE NOT NULL,
	inventory_id integer NOT NULL,
	customer_id smallint NOT NULL,
	return_date timestamp WITHOUT TIME ZONE,
	staff_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
	CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES public.inventory (inventory_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.rental
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE INDEX IF NOT EXISTS idx_fk_inventory_id
 ON ONLY public.rental USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_rental_rental_date_inventory_id_customer_id
 ON ONLY public.rental USING BTREE (rental_date pg_catalog.timestamp_ops ASC NULLS LAST, inventory_id pg_catalog.int4_ops ASC NULLS LAST, customer_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS rental_pkey
 ON ONLY public.rental USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.payment (
	payment_id integer DEFAULT nextval('payment_payment_id_seq'::regclass) NOT NULL,
	customer_id smallint NOT NULL,
	staff_id smallint NOT NULL,
	rental_id integer NOT NULL,
	amount numeric(5,2) NOT NULL,
	payment_date timestamp WITHOUT TIME ZONE NOT NULL,
	CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
	CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES public.rental (rental_id) MATCH SIMPLE ON DELETE SET NULL ON UPDATE CASCADE,
	CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_fk_customer_id
 ON ONLY public.payment USING BTREE (customer_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_rental_id
 ON ONLY public.payment USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE INDEX IF NOT EXISTS idx_fk_staff_id
 ON ONLY public.payment USING BTREE (staff_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS payment_pkey
 ON ONLY public.payment USING BTREE (payment_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE TABLE IF NOT EXISTS public.store (
	store_id integer DEFAULT nextval('store_store_id_seq'::regclass) NOT NULL,
	manager_staff_id smallint NOT NULL,
	address_id smallint NOT NULL,
	last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
	CONSTRAINT store_pkey PRIMARY KEY (store_id),
	CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER last_updated BEFORE UPDATE
	ON public.store
	FOR EACH ROW
	EXECUTE FUNCTION last_updated();

CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_manager_staff_id
 ON ONLY public.store USING BTREE (manager_staff_id pg_catalog.int2_ops ASC NULLS LAST) ;

CREATE UNIQUE INDEX IF NOT EXISTS store_pkey
 ON ONLY public.store USING BTREE (store_id pg_catalog.int4_ops ASC NULLS LAST) ;

CREATE VIEW public.actor_info
AS SELECT actor.actor_id,
		actor.first_name,
		actor.last_name,
		
	FROM actor;

CREATE VIEW public.customer_list
AS SELECT customer.customer_id as id,
		customer.first_name as name,
		address.address,
		address.postal_code as "zip code",
		address.phone,
		city.city,
		country.country,
		customer.activebool as notes,
		customer.store_id as sid
	FROM customer, address, city, country;

CREATE VIEW public.film_list
AS SELECT film.film_id as fid,
		film.title,
		film.description,
		category.name as category,
		film.rental_rate as price,
		film.length,
		film.rating,
		actor.first_name as actors
	FROM film, category, actor;

CREATE VIEW public.nicer_but_slower_film_list
AS SELECT film.film_id as fid,
		film.title,
		film.description,
		category.name as category,
		film.rental_rate as price,
		film.length,
		film.rating,
		actor.first_name as actors
	FROM film, category, actor;

CREATE VIEW public.sales_by_film_category
AS SELECT category.name as category,
		payment.amount as total_sales
	FROM category, payment;

CREATE VIEW public.sales_by_store
AS SELECT city.city as store,
		actor.first_name as manager,
		payment.amount as total_sales
	FROM city, actor, payment;

CREATE VIEW public.staff_list
AS SELECT staff.staff_id as id,
		staff.first_name as name,
		address.address,
		address.postal_code as "zip code",
		address.phone,
		city.city,
		country.country,
		staff.store_id as sid
	FROM staff, address, city, country;
3. Views
3.1 View actor_info
3.1.1 actor_info Tree Diagram
3.1.2 actor_info Properties
PropertyValue
View nameactor_info
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.1.3 actor_info Column
ColumnTypeReqKeyDescriptionComments
actor_idintegerfalse
first_namevarchar(45)false
last_namevarchar(45)false
film_inforeferencefalse
3.1.3.1 Column actor_id
3.1.3.1.1 actor_id Tree Diagram
3.1.3.1.2 actor_id properties
PropertyValue
Business Name actor_id
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.1.3.2 Column first_name
3.1.3.2.1 first_name Tree Diagram
3.1.3.2.2 first_name properties
PropertyValue
Business Name first_name
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.1.3.3 Column last_name
3.1.3.3.1 last_name Tree Diagram
3.1.3.3.2 last_name properties
PropertyValue
Business Name last_name
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.1.3.4 Column film_info
3.1.3.4.1 film_info Tree Diagram
3.1.3.4.2 film_info properties
PropertyValue
Business Name film_info
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.1.4 actor_info JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "actor_info",
    "properties": {
        "actor_id": {
            "type": "number",
            "default": "nextval('actor_actor_id_seq'::regclass)"
        },
        "first_name": {
            "type": "string"
        },
        "last_name": {
            "type": "string"
        },
        "film_info": {}
    },
    "required": [
        "actor_id",
        "first_name",
        "last_name"
    ]
}
3.1.5 actor_info JSON data
{
    "actor_id": "nextval('actor_actor_id_seq'::regclass)",
    "first_name": "Lorem",
    "last_name": "Lorem",
    "film_info": "Lorem"
}
3.2 View customer_list
3.2.1 customer_list Tree Diagram
3.2.2 customer_list Properties
PropertyValue
View namecustomer_list
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.2.3 customer_list Column
ColumnTypeReqKeyDescriptionComments
idintegerfalse
namevarchar(45)false
addressvarchar(50)false
zip codevarchar(10)false
phonevarchar(20)false
cityvarchar(50)false
countryvarchar(50)false
notesbooleanfalse
sidsmallintfalse
3.2.3.1 Column id
3.2.3.1.1 id Tree Diagram
3.2.3.1.2 id properties
PropertyValue
Business Name id
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.2 Column name
3.2.3.2.1 name Tree Diagram
3.2.3.2.2 name properties
PropertyValue
Business Name name
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.3 Column address
3.2.3.3.1 address Tree Diagram
3.2.3.3.2 address properties
PropertyValue
Business Name address
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.4 Column zip code
3.2.3.4.1 zip code Tree Diagram
3.2.3.4.2 zip code properties
PropertyValue
Business Name zip code
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.5 Column phone
3.2.3.5.1 phone Tree Diagram
3.2.3.5.2 phone properties
PropertyValue
Business Name phone
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.6 Column city
3.2.3.6.1 city Tree Diagram
3.2.3.6.2 city properties
PropertyValue
Business Name city
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.7 Column country
3.2.3.7.1 country Tree Diagram
3.2.3.7.2 country properties
PropertyValue
Business Name country
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.8 Column notes
3.2.3.8.1 notes Tree Diagram
3.2.3.8.2 notes properties
PropertyValue
Business Name notes
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.3.9 Column sid
3.2.3.9.1 sid Tree Diagram
3.2.3.9.2 sid properties
PropertyValue
Business Name sid
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.2.4 customer_list JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "customer_list",
    "properties": {
        "id": {
            "type": "number",
            "default": "nextval('customer_customer_id_seq'::regclass)"
        },
        "name": {
            "type": "string"
        },
        "address": {
            "type": "string"
        },
        "zip code": {
            "type": "string"
        },
        "phone": {
            "type": "string"
        },
        "city": {
            "type": "string"
        },
        "country": {
            "type": "string"
        },
        "notes": {
            "type": "boolean",
            "default": true
        },
        "sid": {
            "type": "number"
        }
    },
    "required": [
        "id",
        "name",
        "address",
        "phone",
        "city",
        "country",
        "notes",
        "sid"
    ]
}
3.2.5 customer_list JSON data
{
    "id": "nextval('customer_customer_id_seq'::regclass)",
    "name": "Lorem",
    "address": "Lorem",
    "zip code": "Lorem",
    "phone": "Lorem",
    "city": "Lorem",
    "country": "Lorem",
    "notes": true,
    "sid": -11
}
3.3 View film_list
3.3.1 film_list Tree Diagram
3.3.2 film_list Properties
PropertyValue
View namefilm_list
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.3.3 film_list Column
ColumnTypeReqKeyDescriptionComments
fidintegerfalse
titlevarchar(255)false
descriptiontextfalse
categoryvarchar(25)false
pricenumeric(4, 2)false
lengthsmallintfalse
ratingenumfalse
actorsvarchar(45)false
3.3.3.1 Column fid
3.3.3.1.1 fid Tree Diagram
3.3.3.1.2 fid properties
PropertyValue
Business Name fid
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.2 Column title
3.3.3.2.1 title Tree Diagram
3.3.3.2.2 title properties
PropertyValue
Business Name title
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.3 Column description
3.3.3.3.1 description Tree Diagram
3.3.3.3.2 description properties
PropertyValue
Business Name description
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.4 Column category
3.3.3.4.1 category Tree Diagram
3.3.3.4.2 category properties
PropertyValue
Business Name category
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.5 Column price
3.3.3.5.1 price Tree Diagram
3.3.3.5.2 price properties
PropertyValue
Business Name price
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.6 Column length
3.3.3.6.1 length Tree Diagram
3.3.3.6.2 length properties
PropertyValue
Business Name length
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.7 Column rating
3.3.3.7.1 rating Tree Diagram
3.3.3.7.2 rating properties
PropertyValue
Business Name rating
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.3.8 Column actors
3.3.3.8.1 actors Tree Diagram
3.3.3.8.2 actors properties
PropertyValue
Business Name actors
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.3.4 film_list JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "film_list",
    "properties": {
        "fid": {
            "type": "number",
            "default": "nextval('film_film_id_seq'::regclass)"
        },
        "title": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "category": {
            "type": "string"
        },
        "price": {
            "type": "number",
            "default": "4.99"
        },
        "length": {
            "type": "number"
        },
        "rating": {
            "type": "string",
            "enum": [
                "G",
                "PG",
                "PG-13",
                "R",
                "NC-17"
            ]
        },
        "actors": {
            "type": "string"
        }
    },
    "required": [
        "fid",
        "title",
        "category",
        "price",
        "actors"
    ]
}
3.3.5 film_list JSON data
{
    "fid": "nextval('film_film_id_seq'::regclass)",
    "title": "Lorem",
    "description": "Lorem",
    "category": "Lorem",
    "price": "4.99",
    "length": 5,
    "rating": "NC-17",
    "actors": "Lorem"
}
3.4 View nicer_but_slower_film_list
3.4.1 nicer_but_slower_film_list Tree Diagram
3.4.2 nicer_but_slower_film_list Properties
PropertyValue
View namenicer_but_slower_film_list
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.4.3 nicer_but_slower_film_list Column
ColumnTypeReqKeyDescriptionComments
fidintegerfalse
titlevarchar(255)false
descriptiontextfalse
categoryvarchar(25)false
pricenumeric(4, 2)false
lengthsmallintfalse
ratingenumfalse
actorsvarchar(45)false
3.4.3.1 Column fid
3.4.3.1.1 fid Tree Diagram
3.4.3.1.2 fid properties
PropertyValue
Business Name fid
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.2 Column title
3.4.3.2.1 title Tree Diagram
3.4.3.2.2 title properties
PropertyValue
Business Name title
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.3 Column description
3.4.3.3.1 description Tree Diagram
3.4.3.3.2 description properties
PropertyValue
Business Name description
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.4 Column category
3.4.3.4.1 category Tree Diagram
3.4.3.4.2 category properties
PropertyValue
Business Name category
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.5 Column price
3.4.3.5.1 price Tree Diagram
3.4.3.5.2 price properties
PropertyValue
Business Name price
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.6 Column length
3.4.3.6.1 length Tree Diagram
3.4.3.6.2 length properties
PropertyValue
Business Name length
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.7 Column rating
3.4.3.7.1 rating Tree Diagram
3.4.3.7.2 rating properties
PropertyValue
Business Name rating
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.3.8 Column actors
3.4.3.8.1 actors Tree Diagram
3.4.3.8.2 actors properties
PropertyValue
Business Name actors
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.4.4 nicer_but_slower_film_list JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "nicer_but_slower_film_list",
    "properties": {
        "fid": {
            "type": "number",
            "default": "nextval('film_film_id_seq'::regclass)"
        },
        "title": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "category": {
            "type": "string"
        },
        "price": {
            "type": "number",
            "default": "4.99"
        },
        "length": {
            "type": "number"
        },
        "rating": {
            "type": "string",
            "enum": [
                "G",
                "PG",
                "PG-13",
                "R",
                "NC-17"
            ]
        },
        "actors": {
            "type": "string"
        }
    },
    "required": [
        "fid",
        "title",
        "category",
        "price",
        "actors"
    ]
}
3.4.5 nicer_but_slower_film_list JSON data
{
    "fid": "nextval('film_film_id_seq'::regclass)",
    "title": "Lorem",
    "description": "Lorem",
    "category": "Lorem",
    "price": "4.99",
    "length": -82,
    "rating": "NC-17",
    "actors": "Lorem"
}
3.5 View sales_by_film_category
3.5.1 sales_by_film_category Tree Diagram
3.5.2 sales_by_film_category Properties
PropertyValue
View namesales_by_film_category
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.5.3 sales_by_film_category Column
ColumnTypeReqKeyDescriptionComments
categoryvarchar(25)false
total_salesnumeric(5, 2)false
3.5.3.1 Column category
3.5.3.1.1 category Tree Diagram
3.5.3.1.2 category properties
PropertyValue
Business Name category
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.5.3.2 Column total_sales
3.5.3.2.1 total_sales Tree Diagram
3.5.3.2.2 total_sales properties
PropertyValue
Business Name total_sales
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.5.4 sales_by_film_category JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "sales_by_film_category",
    "properties": {
        "category": {
            "type": "string"
        },
        "total_sales": {
            "type": "number"
        }
    },
    "required": [
        "category",
        "total_sales"
    ]
}
3.5.5 sales_by_film_category JSON data
{
    "category": "Lorem",
    "total_sales": 56
}
3.6 View sales_by_store
3.6.1 sales_by_store Tree Diagram
3.6.2 sales_by_store Properties
PropertyValue
View namesales_by_store
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.6.3 sales_by_store Column
ColumnTypeReqKeyDescriptionComments
storevarchar(50)false
managervarchar(45)false
total_salesnumeric(5, 2)false
3.6.3.1 Column store
3.6.3.1.1 store Tree Diagram
3.6.3.1.2 store properties
PropertyValue
Business Name store
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.6.3.2 Column manager
3.6.3.2.1 manager Tree Diagram
3.6.3.2.2 manager properties
PropertyValue
Business Name manager
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.6.3.3 Column total_sales
3.6.3.3.1 total_sales Tree Diagram
3.6.3.3.2 total_sales properties
PropertyValue
Business Name total_sales
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.6.4 sales_by_store JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "sales_by_store",
    "properties": {
        "store": {
            "type": "string"
        },
        "manager": {
            "type": "string"
        },
        "total_sales": {
            "type": "number"
        }
    },
    "required": [
        "store",
        "manager",
        "total_sales"
    ]
}
3.6.5 sales_by_store JSON data
{
    "store": "Lorem",
    "manager": "Lorem",
    "total_sales": -64
}
3.7 View staff_list
3.7.1 staff_list Tree Diagram
3.7.2 staff_list Properties
PropertyValue
View namestaff_list
Technical name
Activatedtrue
Id
Schemapublic
Additional properties
Comments
Or replacefalse
Temporaryfalse
Recursivefalse
View option
[1] Check testing scope
Security barrier
Security invoker
As query
With check optionfalse
Remarks
3.7.3 staff_list Column
ColumnTypeReqKeyDescriptionComments
idintegerfalse
namevarchar(45)false
addressvarchar(50)false
zip codevarchar(10)false
phonevarchar(20)false
cityvarchar(50)false
countryvarchar(50)false
sidsmallintfalse
3.7.3.1 Column id
3.7.3.1.1 id Tree Diagram
3.7.3.1.2 id properties
PropertyValue
Business Name id
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.2 Column name
3.7.3.2.1 name Tree Diagram
3.7.3.2.2 name properties
PropertyValue
Business Name name
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.3 Column address
3.7.3.3.1 address Tree Diagram
3.7.3.3.2 address properties
PropertyValue
Business Name address
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.4 Column zip code
3.7.3.4.1 zip code Tree Diagram
3.7.3.4.2 zip code properties
PropertyValue
Business Name zip code
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.5 Column phone
3.7.3.5.1 phone Tree Diagram
3.7.3.5.2 phone properties
PropertyValue
Business Name phone
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.6 Column city
3.7.3.6.1 city Tree Diagram
3.7.3.6.2 city properties
PropertyValue
Business Name city
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.7 Column country
3.7.3.7.1 country Tree Diagram
3.7.3.7.2 country properties
PropertyValue
Business Name country
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.3.8 Column sid
3.7.3.8.1 sid Tree Diagram
3.7.3.8.2 sid properties
PropertyValue
Business Name sid
Technical name
Activated true
Reference type collectionReference
Reference description
Lineage capture
Description
Transformation
Sources
3.7.4 staff_list JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "staff_list",
    "properties": {
        "id": {
            "type": "number",
            "default": "nextval('staff_staff_id_seq'::regclass)"
        },
        "name": {
            "type": "string"
        },
        "address": {
            "type": "string"
        },
        "zip code": {
            "type": "string"
        },
        "phone": {
            "type": "string"
        },
        "city": {
            "type": "string"
        },
        "country": {
            "type": "string"
        },
        "sid": {
            "type": "number"
        }
    },
    "required": [
        "id",
        "name",
        "address",
        "phone",
        "city",
        "country",
        "sid"
    ]
}
3.7.5 staff_list JSON data
{
    "id": "nextval('staff_staff_id_seq'::regclass)",
    "name": "Lorem",
    "address": "Lorem",
    "zip code": "Lorem",
    "phone": "Lorem",
    "city": "Lorem",
    "country": "Lorem",
    "sid": 3
}
4. Relationships
4.1 Relationship New Relationship
4.1.1 New Relationship Diagram
Parent TableParent Column
Child TableChild Column
4.1.2 New Relationship Properties
PropertyValue
Name New Relationship
Description
Parent Table
Parent Column
Parent Cardinality 1
Child Table
Child Column
Child Cardinality 0..n
Comments
4.2 Relationship customer_address_id_fkey
4.2.1 customer_address_id_fkey Diagram
Parent TableParent Column
address address_id
Child TableChild Column
customer address_id
4.2.2 customer_address_id_fkey Properties
PropertyValue
Name customer_address_id_fkey
Description
Parent Tableaddress
Parent Columnaddress_id
Parent Cardinality 1
Child Tablecustomer
Child Columnaddress_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.3 Relationship film_actor_actor_id_fkey
4.3.1 film_actor_actor_id_fkey Diagram
Parent TableParent Column
actor actor_id
Child TableChild Column
film_actor actor_id
4.3.2 film_actor_actor_id_fkey Properties
PropertyValue
Name film_actor_actor_id_fkey
Description
Parent Tableactor
Parent Columnactor_id
Parent Cardinality 1
Child Tablefilm_actor
Child Columnactor_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.4 Relationship film_actor_film_id_fkey
4.4.1 film_actor_film_id_fkey Diagram
Parent TableParent Column
film film_id
Child TableChild Column
film_actor film_id
4.4.2 film_actor_film_id_fkey Properties
PropertyValue
Name film_actor_film_id_fkey
Description
Parent Tablefilm
Parent Columnfilm_id
Parent Cardinality 1
Child Tablefilm_actor
Child Columnfilm_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.5 Relationship film_category_category_id_fkey
4.5.1 film_category_category_id_fkey Diagram
Parent TableParent Column
category category_id
Child TableChild Column
film_category category_id
4.5.2 film_category_category_id_fkey Properties
PropertyValue
Name film_category_category_id_fkey
Description
Parent Tablecategory
Parent Columncategory_id
Parent Cardinality 1
Child Tablefilm_category
Child Columncategory_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.6 Relationship film_category_film_id_fkey
4.6.1 film_category_film_id_fkey Diagram
Parent TableParent Column
film film_id
Child TableChild Column
film_category film_id
4.6.2 film_category_film_id_fkey Properties
PropertyValue
Name film_category_film_id_fkey
Description
Parent Tablefilm
Parent Columnfilm_id
Parent Cardinality 1
Child Tablefilm_category
Child Columnfilm_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.7 Relationship film_language_id_fkey
4.7.1 film_language_id_fkey Diagram
Parent TableParent Column
language language_id
Child TableChild Column
film language_id
4.7.2 film_language_id_fkey Properties
PropertyValue
Name film_language_id_fkey
Description
Parent Tablelanguage
Parent Columnlanguage_id
Parent Cardinality 1
Child Tablefilm
Child Columnlanguage_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.8 Relationship fk_address_city
4.8.1 fk_address_city Diagram
Parent TableParent Column
city city_id
Child TableChild Column
address city_id
4.8.2 fk_address_city Properties
PropertyValue
Name fk_address_city
Description
Parent Tablecity
Parent Columncity_id
Parent Cardinality 1
Child Tableaddress
Child Columncity_id
Child Cardinality 0..n
Comments
relationshipOnDeleteNO ACTION
4.9 Relationship fk_city
4.9.1 fk_city Diagram
Parent TableParent Column
country country_id
Child TableChild Column
city country_id
4.9.2 fk_city Properties
PropertyValue
Name fk_city
Description
Parent Tablecountry
Parent Columncountry_id
Parent Cardinality 1
Child Tablecity
Child Columncountry_id
Child Cardinality 0..n
Comments
relationshipOnDeleteNO ACTION
4.10 Relationship inventory_film_id_fkey
4.10.1 inventory_film_id_fkey Diagram
Parent TableParent Column
film film_id
Child TableChild Column
inventory film_id
4.10.2 inventory_film_id_fkey Properties
PropertyValue
Name inventory_film_id_fkey
Description
Parent Tablefilm
Parent Columnfilm_id
Parent Cardinality 1
Child Tableinventory
Child Columnfilm_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.11 Relationship payment_customer_id_fkey
4.11.1 payment_customer_id_fkey Diagram
Parent TableParent Column
customer customer_id
Child TableChild Column
payment customer_id
4.11.2 payment_customer_id_fkey Properties
PropertyValue
Name payment_customer_id_fkey
Description
Parent Tablecustomer
Parent Columncustomer_id
Parent Cardinality 1
Child Tablepayment
Child Columncustomer_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.12 Relationship payment_rental_id_fkey
4.12.1 payment_rental_id_fkey Diagram
Parent TableParent Column
rental rental_id
Child TableChild Column
payment rental_id
4.12.2 payment_rental_id_fkey Properties
PropertyValue
Name payment_rental_id_fkey
Description
Parent Tablerental
Parent Columnrental_id
Parent Cardinality 1
Child Tablepayment
Child Columnrental_id
Child Cardinality 0..n
Comments
relationshipOnDeleteSET NULL
4.13 Relationship payment_staff_id_fkey
4.13.1 payment_staff_id_fkey Diagram
Parent TableParent Column
staff staff_id
Child TableChild Column
payment staff_id
4.13.2 payment_staff_id_fkey Properties
PropertyValue
Name payment_staff_id_fkey
Description
Parent Tablestaff
Parent Columnstaff_id
Parent Cardinality 1
Child Tablepayment
Child Columnstaff_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.14 Relationship rental_customer_id_fkey
4.14.1 rental_customer_id_fkey Diagram
Parent TableParent Column
customer customer_id
Child TableChild Column
rental customer_id
4.14.2 rental_customer_id_fkey Properties
PropertyValue
Name rental_customer_id_fkey
Description
Parent Tablecustomer
Parent Columncustomer_id
Parent Cardinality 1
Child Tablerental
Child Columncustomer_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.15 Relationship rental_inventory_id_fkey
4.15.1 rental_inventory_id_fkey Diagram
Parent TableParent Column
inventory inventory_id
Child TableChild Column
rental inventory_id
4.15.2 rental_inventory_id_fkey Properties
PropertyValue
Name rental_inventory_id_fkey
Description
Parent Tableinventory
Parent Columninventory_id
Parent Cardinality 1
Child Tablerental
Child Columninventory_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.16 Relationship rental_staff_id_key
4.16.1 rental_staff_id_key Diagram
Parent TableParent Column
staff staff_id
Child TableChild Column
rental staff_id
4.16.2 rental_staff_id_key Properties
PropertyValue
Name rental_staff_id_key
Description
Parent Tablestaff
Parent Columnstaff_id
Parent Cardinality 1
Child Tablerental
Child Columnstaff_id
Child Cardinality 0..n
Comments
relationshipOnDeleteNO ACTION
4.17 Relationship staff_address_id_fkey
4.17.1 staff_address_id_fkey Diagram
Parent TableParent Column
address address_id
Child TableChild Column
staff address_id
4.17.2 staff_address_id_fkey Properties
PropertyValue
Name staff_address_id_fkey
Description
Parent Tableaddress
Parent Columnaddress_id
Parent Cardinality 1
Child Tablestaff
Child Columnaddress_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.18 Relationship store_address_id_fkey
4.18.1 store_address_id_fkey Diagram
Parent TableParent Column
address address_id
Child TableChild Column
store address_id
4.18.2 store_address_id_fkey Properties
PropertyValue
Name store_address_id_fkey
Description
Parent Tableaddress
Parent Columnaddress_id
Parent Cardinality 1
Child Tablestore
Child Columnaddress_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT
4.19 Relationship store_manager_staff_id_fkey
4.19.1 store_manager_staff_id_fkey Diagram
Parent TableParent Column
staff staff_id
Child TableChild Column
store manager_staff_id
4.19.2 store_manager_staff_id_fkey Properties
PropertyValue
Name store_manager_staff_id_fkey
Description
Parent Tablestaff
Parent Columnstaff_id
Parent Cardinality 1
Child Tablestore
Child Columnmanager_staff_id
Child Cardinality 0..n
Comments
relationshipOnDeleteRESTRICT